﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using System.Collections;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESMenuBusiness
    {
        /// <summary>
        /// 获取菜单信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetMenuData(Dictionary<string, string> para)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT DISTINCT m.menuname,m.createdate,e.fullname,m.id");
            strSql.AppendLine("FROM menu m");
            strSql.AppendLine("LEFT JOIN employee e ON e.employeeid = m.createempid");
            strSql.AppendLine("WHERE 1=1");
            string condition = "";
            if (para.ContainsKey("MenuName"))
            {
                condition += string.Format(" and m.menuname like '%{0}%'", para["MenuName"]);
            }

            if (para.ContainsKey("MenuName1"))
            {
                condition += string.Format(" and m.menuname ='{0}'", para["MenuName1"]);
            }

            //if (para.ContainsKey("MenuChidName"))
            //{
            //    condition += string.Format(" and mc.menuchildname like '%{0}%'", para["MenuChidName"]);
            //}

            strSql.AppendLine(condition);

            strSql.AppendLine("ORDER BY m.menuname,m.createdate");

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }


        public uMESPagingDataDTO GetMenuDataold(Dictionary<string, string> para)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT m.menuname,m.createdate,e.fullname,mc.id,mc.mainid,mc.menuchildname,mc.url,mc.sequence");
            strSql.AppendLine("FROM menu m");
            strSql.AppendLine("LEFT JOIN menuchild mc ON mc.mainid=m.id");
            strSql.AppendLine("LEFT JOIN employee e ON e.employeeid = m.createempid");
            strSql.AppendLine("WHERE 1=1");
            string condition = "";
            if (para.ContainsKey("MenuName"))
            {
                condition += string.Format(" and m.menuname like '%{0}%'", para["MenuName"]);
            }

            if (para.ContainsKey("MenuChidName"))
            {
                condition += string.Format(" and mc.menuchildname like '%{0}%'", para["MenuChidName"]);
            }

            strSql.AppendLine(condition);

            strSql.AppendLine("ORDER BY m.menuname,mc.sequence");

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }

        /// <summary>
        /// 查询主菜单下的子菜单列表
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public DataTable GetMenuChid(string strID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT mc.menuchildname,mc.url,mc.id,mc.mainid,mc.sequence");
            strSql.AppendLine("FROM menuchild mc");
            strSql.AppendLine("WHERE mc.mainid='" + strID + "'");
            strSql.AppendLine("ORDER BY mc.sequence");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 删除菜单
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public Boolean DelMenu(string strID)
        {
            ArrayList SQLStringList = new ArrayList();

            //删除主表
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE menu WHERE id = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());

            //删除子菜单
            strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE menuchild WHERE mainid = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());
            OracleHelper.ExecuteSqlTran(SQLStringList);
            return true;
        }

        /// <summary>
        /// 保存菜单
        /// </summary>
        /// <param name="para"></param>
        /// <param name="dtMenuChild"></param>
        /// <returns></returns>
        public ResultModel SaveMenuInfo(Dictionary<string, string> para, DataTable dtMenuChild)
        {
            ResultModel result = new ResultModel(false, "");
            ArrayList SQLStringList = new ArrayList();

            //主表
            string strID = Guid.NewGuid().ToString();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO menu(id,menuname,createempid,createdate)");
            strSQL.AppendLine("VALUES (");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["menuname"]));
            strSQL.AppendLine(string.Format("'{0}',", para["CreateEmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["CreateDate"]));
            strSQL.AppendLine(")");
            SQLStringList.Add(strSQL.ToString());

            //子菜单
            if (dtMenuChild.Rows.Count > 0)
            {
                foreach (DataRow row in dtMenuChild.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO menuchild(id,mainid,menuchildname,url,sequence)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["menuchildname"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["url"]));
                    strSQL.AppendLine(string.Format("'{0}'", row["sequence"]));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                }
            }
            OracleHelper.ExecuteSqlTran(SQLStringList);
            result.IsSuccess = true;result.Data = strID;
            return result;
        }


        /// <summary>
        /// 更新菜单
        /// </summary>
        /// <param name="para"></param>
        /// <param name="dtMenuChild"></param>
        /// <returns></returns>
        public ResultModel UpdateMenu(Dictionary<string, string> para, DataTable dtMenuChild)
        {
            ResultModel result = new ResultModel(false, "");
            ArrayList SQLStringList = new ArrayList();

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE menu SET");
            strSQL.AppendLine(string.Format("menuname = '{0}'", para["menuname"].ToString()));
            strSQL.AppendLine(string.Format("WHERE id = '{0}'", para["mainid"].ToString()));
            SQLStringList.Add(strSQL.ToString());


            //删除子菜单
            strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE menuchild WHERE mainid = '{0}'", para["mainid"].ToString()));
            SQLStringList.Add(strSQL.ToString());

            StringBuilder strSql = new StringBuilder();
            foreach (DataRow row in dtMenuChild.Rows)
            { 
                strSQL = new StringBuilder();
                strSQL.AppendLine("INSERT INTO menuchild(id,mainid,menuchildname,url,sequence)");
                strSQL.AppendLine("VALUES(sys_guid(),");
                strSQL.AppendLine(string.Format("'{0}',", para["mainid"].ToString()));
                strSQL.AppendLine(string.Format("'{0}',", row["menuchildname"].ToString()));
                strSQL.AppendLine(string.Format("'{0}',", row["url"].ToString()));
                strSQL.AppendLine(string.Format("'{0}'", row["sequence"].ToString()));
                strSQL.AppendLine(")");
                SQLStringList.Add(strSQL.ToString());
            }
            OracleHelper.ExecuteSqlTran(SQLStringList);
            result.IsSuccess = true;
            return result;
        }


        /// <summary>
        /// 查询主菜单是否与设备关联
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public DataTable GetMenuRole(string strMenuID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT rm.menuname,rm.menuid");
            strSql.AppendLine("FROM rolemenu rm");
            strSql.AppendLine("WHERE rm.menuid='" + strMenuID + "'"); 
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }
    }

}
